using Dapper;
using InventoryIssue.API.Read.Application.Command.Allocate;
using InventoryIssue.Dtos;
using InventoryIssue.ErrorCode;
using InventoryIssue.Infrastructrue.Implement;
using MediatR;
using System.Text;

namespace InventoryIssue.API.Read.Application.Handler.Allocate
{
    public class GetInboundRecordHandler : IRequestHandler<GetInboundRecordCommand, APIPaging<InboundRecordDto>>
    {
        private readonly DatabaseContext db;

        public GetInboundRecordHandler(DatabaseContext db)
        {
            this.db = db;
        }

        public async Task<APIPaging<InboundRecordDto>> Handle(GetInboundRecordCommand request, CancellationToken cancellationToken)
        {
            APIPaging<InboundRecordDto> result = new APIPaging<InboundRecordDto>();
            StringBuilder sql = new StringBuilder();
            StringBuilder countSql = new StringBuilder();

            sql.Append(@"
                SELECT
                    i.Id,
                    i.InboundRecordId,
                    i.AllocateId,
                    i.InboundDate,
                    p.ProductName,
                    p.ProductCode,
                    p.Specifications,
                    i.Quantity,
                    i.InboundWarehouse,
                    i.InboundLocation,
                    i.InboundOperator,
                    i.Note,
                    a.OrderNumber
                FROM
                    inboundrecordmodel i
                    JOIN allocatemodel a ON i.AllocateId = a.AllocateId
                    JOIN allocateproductmodel ap ON a.AllocateId = ap.AllocateId
                    JOIN productmodel p ON ap.ProductId = p.ProductId
                WHERE
                    i.IsDel = 0 
                    AND a.IsDel = 0 
                    AND ap.IsDel = 0
                    AND p.IsDel = 0
            ");

            countSql.Append(@"
                SELECT COUNT(i.Id)
                FROM inboundrecordmodel i
                    JOIN allocatemodel a ON i.AllocateId = a.AllocateId
                    JOIN allocateproductmodel ap ON a.AllocateId = ap.AllocateId
                    JOIN productmodel p ON ap.ProductId = p.ProductId
                WHERE i.IsDel = 0 AND a.IsDel = 0 AND ap.IsDel = 0 AND p.IsDel = 0
            ");

            // 添加筛选条件
            if (request.AllocateId.HasValue)
            {
                sql.Append(" AND i.AllocateId = @AllocateId");
                countSql.Append(" AND i.AllocateId = @AllocateId");
            }

            if (request.InboundDateStart.HasValue)
            {
                sql.Append(" AND i.InboundDate >= @InboundDateStart");
                countSql.Append(" AND i.InboundDate >= @InboundDateStart");
            }

            if (request.InboundDateEnd.HasValue)
            {
                sql.Append(" AND i.InboundDate <= @InboundDateEnd");
                countSql.Append(" AND i.InboundDate <= @InboundDateEnd");
            }

            if (!string.IsNullOrEmpty(request.InboundWarehouse))
            {
                sql.Append(" AND i.InboundWarehouse LIKE @InboundWarehouse");
                countSql.Append(" AND i.InboundWarehouse LIKE @InboundWarehouse");
            }

            if (!string.IsNullOrEmpty(request.ProductName))
            {
                sql.Append(" AND p.ProductName LIKE @ProductName");
                countSql.Append(" AND p.ProductName LIKE @ProductName");
            }

            if (!string.IsNullOrEmpty(request.InboundOperator))
            {
                sql.Append(" AND i.InboundOperator LIKE @InboundOperator");
                countSql.Append(" AND i.InboundOperator LIKE @InboundOperator");
            }

            sql.Append(" ORDER BY i.InboundDate DESC LIMIT @PageSize OFFSET @offset");

            var parameters = new
            {
                AllocateId = request.AllocateId,
                InboundDateStart = request.InboundDateStart,
                InboundDateEnd = request.InboundDateEnd,
                InboundWarehouse = $"%{request.InboundWarehouse}%",
                ProductName = $"%{request.ProductName}%",
                InboundOperator = $"%{request.InboundOperator}%",
                PageSize = request.PageSize ?? 15,
                offset = ((request.PageIndex ?? 1) - 1) * (request.PageSize ?? 15)
            };

            List<InboundRecordDto> pageDatas;
            int totalCount;
            
            try
            {
                pageDatas = (await db.DapperConnection.QueryAsync<InboundRecordDto>(sql.ToString(), parameters)).ToList();
                totalCount = await db.DapperConnection.ExecuteScalarAsync<int>(countSql.ToString(), parameters);
            }
            catch (Exception ex) when (ex.Message.Contains("doesn't exist") || ex.Message.Contains("不存在"))
            {
                // 如果表不存在，返回空数据
                pageDatas = new List<InboundRecordDto>();
                totalCount = 0;
            }
            
            int totalPage = (int)Math.Ceiling(totalCount / (double)(request.PageSize ?? 15));

            result.Message = "查询成功";
            result.TotalPage = totalPage;
            result.TotalCount = totalCount;
            result.PageDatas = pageDatas.ToList();

            return result;
        }
    }
} 